const {
    sqlExec,
    sqlExecInTransaction,
    startTransaction,
    commitTransaction,
    rollbackTransaction
} = require('../db/mysql57');

const uploadExcelDao = {
    // 查询某个用户的所有上传文件
    async getAllFiles() {
        let sql = `select id, fileName as name, fileType as type from ExcelDemo1_File`;
        return sqlExec(sql);
    },
    // 上传Excel
    async uploadExcel(fileName, excel_data) {
        // 判断该文件是否存在
        let sql = `select count(*) as fileCount from ExcelDemo1_File where fileName = ?`;
        let data = [ fileName ];
        let res = await sqlExec(sql, data);
        if (res[0].fileCount > 0) {
            throw "该文件已存在，请检查是否重名"
        }
        // 开启事务
        let connection;
        try {
            connection = await startTransaction();
        } catch (err) {
            console.log(err);
            throw "数据库异常";
        }
        // 执行事务代码
        try {
            // 创建该文件
            let sql = `INSERT INTO ExcelDemo1_File(fileName) VALUES (?)`;
            let data = [ fileName ];
            let res = await sqlExecInTransaction(connection, sql, data);
            // 获取文件ID
            let fileId = res.insertId;
            // 循环导入一级事项到该文件中
            for (let i = 0; i < excel_data.length; i++) {
                let item = excel_data[i];
                let sql = `INSERT INTO ExcelDemo1_Content (
                    fileId, num, ImplementingInstitution, ItemCategory, ItemName, Basis_for_Setting
                ) VALUES (?, ?, ?, ?, ?, ?)`;
                let data = [fileId, item["序号"], item["实施机构"], item["事项类别"], item["事项名称"], item["设定依据"]];
                let res = await sqlExecInTransaction(connection, sql, data);
                let contentId = res.insertId;
                // 循环导入二级事项到一级事项中
                // 这轮循环后期应该优化为 一次性插入所有二级事项
                for (let j = 0; j < item["二级事项"].length; j++) {
                    let subItem = item["二级事项"][j];
                    let sql = `INSERT INTO ExcelDemo1_SubContent (
                        contentId, subNum, subItemName, Department
                    ) VALUES (?, ?, ?, ?)`;
                    let data = [contentId, subItem["二级序号"], subItem["二级事项名称"], subItem["部门"]];
                    await sqlExecInTransaction(connection, sql, data);
                }
            }
            await commitTransaction(connection);
        } catch (err) {
            rollbackTransaction(connection);
            throw "数据库异常";
        }
    },
    // 下载Excel
    async downloadExcel(id) {
        let sql = `select
                *
            from ExcelDemo1_Content
            where fileId = ?`;
        let data = [ id ];
        return sqlExec(sql, data);
    },

    // 加载二级事项
    async loadSubItem(contentId) {
        let sql = `select
                *
            from ExcelDemo1_SubContent
            where contentId = ?`;
        let data = [ contentId ];
        return sqlExec(sql, data);
    }
};

module.exports = uploadExcelDao;



